--** 创建者   : suning
--** 创建日期 : 20230406
--** 功能描述 ：全链路判责流向汇总
--**************************************************************************
--**************************** 修改日志 ************************************
--**************************************************************************
--** 依赖表：
--**************************************************************************
--*******************  CURRENT_VERSION ： V.1.1  ***************************
drop table jms_tmp.dm_whole_decide_duty_flow_pres_dt;
create external table if not exists jms_tmp.dm_whole_decide_duty_flow_pres_dt(
    search_type                         string          comment'查询类型'
   ,order_source_code                   string          comment'订单来源编码'
   ,order_source_name                   string          comment'订单来源名称'
   ,start_network_code                  string          comment'始发网点编码'
   ,start_network_name                  string          comment'始发网点名称'
   ,start_city_code                     string          comment'始发城市编码'
   ,start_city_name                     string          comment'始发城市名称'
   ,start_provider_code                 string          comment'始发省份编码'
   ,start_provider_name                 string          comment'始发省份名称'
   ,start_fran_code                     string          comment'始发加盟商编码'
   ,start_fran_name                     string          comment'始发加盟商名称'
   ,start_agent_code                    string          comment'始发代理区编码'
   ,start_agent_name                    string          comment'始发代理区名称'
   ,start_center_code                   string          comment'始发中心编码'
   ,start_center_name                   string          comment'始发中心名称'
   ,end_network_code                    string          comment'目的网点编码'
   ,end_network_name                    string          comment'目的网点名称'
   ,end_city_code                       string          comment'目的城市编码'
   ,end_city_name                       string          comment'目的城市名称'
   ,end_provider_code                   string          comment'目的省份编码'
   ,end_provider_name                   string          comment'目的省份名称'
   ,end_fran_code                       string          comment'目的加盟商编码'
   ,end_fran_name                       string          comment'目的加盟商名称'
   ,end_agent_code                      string          comment'目的代理区编码'
   ,end_agent_name                      string          comment'目的代理区名称'
   ,end_center_code                     string          comment'目的中心编码'
   ,end_center_name                     string          comment'目的中心名称'
   ,jingtai_lyqs_cnt                    bigint          comment'七星潭静态路由缺失票数'
   ,dizhi_jxcw_cnt                      bigint          comment'七星潭地址解析错误票数'
   ,jingtai_my_cnt                      bigint          comment'七星潭静态慢于票数'
   ,jiedanhou_ls_cnt                    bigint          comment'七星潭截单后揽收票数'
   ,jiaojian_jbcw_cnt                   bigint          comment'七星潭交件建包错误票数'
   ,cuojiao_cnt                         bigint          comment'七星潭错交票数'
   ,jiaojian_bzd_cnt                    bigint          comment'七星潭交件不准点票数'
   ,zhongzhuan_jbcw_cnt                 bigint          comment'七星潭中转建包错误票数'
   ,cuofa_cnt                           bigint          comment'七星潭错发票数'
   ,fengche_wd_cnt                      bigint          comment'七星潭封车晚点票数'
   ,caozuo_yw_cnt                       bigint          comment'七星潭操作延误票数'
   ,fache_wd_cnt                        bigint          comment'七星潭发车晚点票数'
   ,yunshu_wd_cnt                       bigint          comment'七星潭运输晚点票数'
   ,jingang_zybjs_cnt                   bigint          comment'七星潭进港转运不及时票数 '
   ,zhongzhuan_cf_cnt                   bigint          comment'七星潭中转错分票数'
   ,sanhuo_bjs_cnt                      bigint          comment'七星潭散货不及时票数'
   ,paijian_cf_cnt                      bigint          comment'七星潭派件错分票数'
   ,qianshou_wd_cnt                     bigint          comment'七星潭签收晚点票数'
   ,tuizhuan_cnt                        bigint          comment'七星潭退转件票数'
   ,duty_cnt                            bigint          comment'七星潭判责总票数(未达成票数)'
   ,zy_dizhi_jxcw_cnt                   bigint          comment'自有平台地址解析错误票数'
   ,zy_jiedanhou_ls_cnt                 bigint          comment'自有平台截单后揽收票数'
   ,zy_jiaojian_jbcw_cnt                bigint          comment'自有平台交件建包错误票数'
   ,zy_cuojiao_cnt                      bigint          comment'自有平台错交票数'
   ,zy_jiaojian_bzd_cnt                 bigint          comment'自有平台交件不准点票数'
   ,zy_zhongzhuan_jbcw_cnt              bigint          comment'自有平台中转建包错误票数'
   ,zy_cuofa_cnt                        bigint          comment'自有平台错发票数'
   ,zy_fengche_wd_cnt                   bigint          comment'自有平台封车晚点票数'
   ,zy_caozuo_yw_cnt                    bigint          comment'自有平台操作延误票数'
   ,zy_fache_wd_cnt                     bigint          comment'自有平台发车晚点票数'
   ,zy_yunshu_wd_cnt                    bigint          comment'自有平台运输晚点票数'
   ,zy_jingang_zybjs_cnt                bigint          comment'自有平台进港转运不及时票数'
   ,zy_zhongzhuan_cf_cnt                bigint          comment'自有平台中转错分票数'
   ,zy_sanhuo_bjs_cnt                   bigint          comment'自有平台散货不及时票数'
   ,zy_paijian_cf_cnt                   bigint          comment'自有平台派件错分票数'
   ,zy_qianshou_wd_cnt                  bigint          comment'自有平台签收晚点票数'
   ,zy_tuizhuan_cnt                     bigint          comment'自有平台退转件票数'
   ,zy_duty_cnt                         bigint          comment'自有平台判责总票数(未达成票数)'
   ,all_cnt                             bigint          comment'达成率总票数'
   ,reach_cnt                           bigint          comment'七星潭达成率达成总票数'
   ,not_reach_cnt                       bigint          comment'七星潭达成率未达成总票数'
   ,zy_reach_cnt                        bigint          comment'自有平台达成率达成总票数'
   ,zy_not_reach_cnt                    bigint          comment'自有平台达成率未达成总票数'
   ,aging_sign_date                     string          comment'时效签收日期'
) comment '全链路判责流向汇总'
partitioned by (dt string comment '分区日期')
stored as parquet
location '/dw/hive/jms_tmp.db/external/dm_whole_decide_duty_flow_pres_dt'
tblproperties (
'discover.partitions'='false',
'parquet.column.index.access'='true'
);
